﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class Device : Basic, IDevice
    {
        //private const string sqlGetDeviceBySiteIDBSIDAndTypeWithAlert = "SELECT distinct d.id,d.identify,d.\"TYPE\",d.\"OBJECT\",d.subobject ,b.\"TIMESTAMP\","
        //    + "b.status  FROM device d  LEFT JOIN (select a.deviceid,MAX(a.\"TIMESTAMP\") AS maxTime from alert a where a.status='Open' GROUP BY "
        //    + "a.deviceid)c ON d.ID=c.deviceid LEFT JOIN alert b ON b.deviceid = d.ID AND c.maxTime=b.\"TIMESTAMP\" WHERE "
        //    + "b.status='Open' AND d.siteid=:siteID AND d.businesssystemid=:businessSystemID ";
        private const string sqlGetDeviceBySiteIDBSIDAndTypeWithAlert = " SELECT d.id,d.identify,d.TYPE,d.OBJECT,d.subobject, " +
        "(SELECT COUNT(id) FROM alert a WHERE a.deviceid= d.id AND a.status='OPEN') AS OpenCount  FROM device d where d.siteid=:siteID " +
        "AND d.businesssystemid=:businessSystemID {0} ";
        //    private const string sqlGetDeviceBySiteIDAndTypeWithAlert = "SELECT distinct d.id,d.identify,d.\"TYPE\",d.\"OBJECT\",d.subobject ,b.\"TIMESTAMP\","
        //+ "b.status  FROM device d  LEFT JOIN (select a.deviceid,MAX(a.\"TIMESTAMP\") AS maxTime from alert a where a.status='Open' GROUP BY "
        //+ "a.deviceid)c ON d.ID=c.deviceid LEFT JOIN alert b ON b.deviceid = d.ID AND c.maxTime=b.\"TIMESTAMP\" WHERE "
        //+ "b.status='Open' AND d.siteid=:siteID ";
        private const string sqlGetDeviceBySiteIDAndTypeWithAlert = "SELECT d.id,d.identify,d.TYPE,d.OBJECT,d.subobject, " +
            "(SELECT COUNT(id) FROM alert a WHERE a.deviceid= d.id AND a.status='OPEN') AS OpenCount   FROM device d where d.siteid=:siteID " +
            " {0}  ";
        private const string sqlGetDeviceAlertCountBySiteID = "SELECT Distinct d.TYPE AS DeviceType, " +
        "(SELECT count(*) FROM alert a INNER JOIN device d2 ON d2.ID = a.deviceid WHERE d2.type=d.type AND a.status='OPEN' AND d2.siteid=d.siteid)  AS AllAlertCount, " +
        "(SELECT count(*) FROM alert a INNER JOIN device d2 ON d2.ID = a.deviceid WHERE d2.type=d.type AND a.status='OPEN' AND a.severity=2 AND d2.siteid=d.siteid) AS CriticalAlertCount, " +
        "(SELECT count(*) FROM alert a INNER JOIN device d2 ON d2.ID = a.deviceid WHERE d2.type=d.type AND a.status='OPEN' AND a.severity=1 AND d2.siteid=d.siteid) AS WarningAlertCount, " +
        "(SELECT count(*) FROM alert a INNER JOIN device d2 ON d2.ID = a.deviceid WHERE d2.type=d.type AND a.status='OPEN' AND a.severity=0 AND d2.siteid=d.siteid) AS NormalAlertCount, " +
        "(SELECT  MAX(TIMESTAMP) FROM alert a INNER JOIN device d2 ON d2.ID = a.deviceid WHERE d2.type=d.type AND a.status='OPEN' AND d2.siteid=d.siteid)  AS LastAlertDate " +
        "FROM device d  WHERE d.siteid=:siteID";
        private const string selectGetDeviceByID = "select * from device where id=:id";
        private const string sqlDelete = "delete from device where id=:id";
        private const string sqlGetDeviceByBSID = "select * from device d where d.BusinessSystemId=:BusinessSystemId {0}";
        private const string sqlGetNoBSIDDeviceByBSID = "select * from device d inner join businesssystem b on b.siteid=d.siteid where d.businesssystemid is null and b.id=:BusinessSystemId";
        private const string sqlGetDeviceSiteGJIPPort2 = "SELECT d.identify,b.Alias as Name,s.gj_ipport2 FROM device d LEFT JOIN businesssystem b ON b.ID=d.businesssystemid LEFT JOIN sitebranch s ON s.ID = d.siteid WHERE d.id=:deviceID";
        private const string sqlGetIsExistNoBSID = "select count(*) from device d where d.identify=:Identify " +
"and d.type=:Type and d.siteID=:SiteID and d.businesssystemid is null ";
        private const string sqlGetIsExist = "select count(*) from device d where d.identify=:Identify and d.type=:Type " +
"and d.siteID=:SiteID and d.businesssystemid=:BusinessSystemID";
        public List<DeviceModel> GetDeviceBySiteIDBSIDAndType(decimal siteID, decimal bsID, string type, string object1, string subobject)
        {
            IList<QueryParameter> listOraclePs = new List<QueryParameter>();
            listOraclePs.Add(new QueryParameter()
            {
                OracleDbColumnName = "SiteID",
                OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = siteID,
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });
            listOraclePs.Add(new QueryParameter()
            {
                OracleDbColumnName = "BusinessSystemId",
                OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = bsID,
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });
            if (!string.IsNullOrWhiteSpace(type))
            {
                listOraclePs.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Type",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 50,
                    OracleDbColumnValue = type,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            if (!string.IsNullOrWhiteSpace(object1))
            {
                listOraclePs.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Object",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 50,
                    OracleDbColumnValue = object1,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            if (!string.IsNullOrWhiteSpace(subobject))
            {
                listOraclePs.Add(new QueryParameter()
                {
                    OracleDbColumnName = "SubObject",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 50,
                    OracleDbColumnValue = subobject,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where
                });
            }
            return ModelHelper<DeviceModel>.GetModel_By_QueryParams(listOraclePs, null, null, null, oda);

        }

        public List<DeviceModel> GetServerBySiteIDBSID(decimal siteID, decimal bsID)
        {
            return GetDeviceBySiteIDBSIDAndType(siteID, bsID, "Server", null, null);
        }


        public List<DeviceAndAlertModel> GetDeviceBySiteIDBSIDAndTypeWithAlert(decimal siteID, decimal bsID, string type, string object1, string subobject)
        {
            List<OracleParameter> listParams = new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="siteID",OracleDbType=OracleDbType.Decimal,Value=siteID},
                     new OracleParameter(){ ParameterName="businessSystemID",OracleDbType=OracleDbType.Decimal,Value=bsID}
                 };
            string sqlAdd = string.Empty;
            if (!string.IsNullOrWhiteSpace(type))
            {
                listParams.Add(new OracleParameter() { ParameterName = "type", OracleDbType = OracleDbType.NVarchar2, Size = 50, Value = type });
                sqlAdd += "AND d.\"TYPE\"=:type ";
            }
            if (!string.IsNullOrWhiteSpace(object1))
            {
                listParams.Add(new OracleParameter() { ParameterName = "object", OracleDbType = OracleDbType.NVarchar2, Size = 50, Value = object1 });
                sqlAdd += "AND d.\"OBJECT\"=:object ";
            }
            if (!string.IsNullOrWhiteSpace(subobject))
            {
                listParams.Add(new OracleParameter() { ParameterName = "subobject", OracleDbType = OracleDbType.NVarchar2, Size = 50, Value = subobject });
                sqlAdd += " AND d.\"OBJECT\"=:object ";
            }
            return ModelHelper<DeviceAndAlertModel>.GetModel_By_DirectSql(string.Format(sqlGetDeviceBySiteIDBSIDAndTypeWithAlert, sqlAdd), listParams, oda);
        }

        public List<DeviceAndAlertModel> GetServerBySiteIDBSIDWithAlert(decimal siteID, decimal bsID)
        {
            return GetDeviceBySiteIDBSIDAndTypeWithAlert(siteID, bsID, "服务器", null, null);
        }

        public List<DeviceAndAlertModel> GetMiddleWareBySiteIDBSIDWithAlert(decimal siteID, decimal bsID)
        {
            return GetDeviceBySiteIDBSIDAndTypeWithAlert(siteID, bsID, "中间件", null, null);
        }

        public List<DeviceAndAlertModel> GetSQLServerBySiteIDBSIDWithAlert(decimal siteID, decimal bsID)
        {
            return GetDeviceBySiteIDBSIDAndTypeWithAlert(siteID, bsID, "数据库", null, null);
        }

        public List<DeviceAndAlertModel> GetDeviceBySiteIDAndTypeWithAlert(decimal siteID, string type, string object1, string subobject)
        {
            List<OracleParameter> listParams = new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="siteID",OracleDbType=OracleDbType.Decimal,Value=siteID},
                 };
            string sqlAdd = string.Empty;
            if (!string.IsNullOrWhiteSpace(type))
            {
                listParams.Add(new OracleParameter() { ParameterName = "type", OracleDbType = OracleDbType.NVarchar2, Size = 50, Value = type });
                sqlAdd += "AND d.\"TYPE\"=:type ";
            }
            if (!string.IsNullOrWhiteSpace(object1))
            {
                listParams.Add(new OracleParameter() { ParameterName = "object", OracleDbType = OracleDbType.NVarchar2, Size = 50, Value = object1 });
                sqlAdd += "AND d.\"OBJECT\"=:object ";
            }
            if (!string.IsNullOrWhiteSpace(subobject))
            {
                listParams.Add(new OracleParameter() { ParameterName = "subobject", OracleDbType = OracleDbType.NVarchar2, Size = 50, Value = subobject });
                sqlAdd += " AND d.\"OBJECT\"=:object ";
            }
            return ModelHelper<DeviceAndAlertModel>.GetModel_By_DirectSql(string.Format(sqlGetDeviceBySiteIDAndTypeWithAlert, sqlAdd), listParams, oda);
        }

        public List<DeviceAndAlertModel> GetServerBySiteIDWithAlert(decimal siteID)
        {
            return GetDeviceBySiteIDAndTypeWithAlert(siteID, "服务器", null, null);
        }

        public List<DeviceAndAlertModel> GetMiddleWareBySiteIDWithAlert(decimal siteID)
        {
            return GetDeviceBySiteIDAndTypeWithAlert(siteID, "中间件", null, null);
        }

        public List<DeviceAndAlertModel> GetSQLServerBySiteIDWithAlert(decimal siteID)
        {
            return GetDeviceBySiteIDAndTypeWithAlert(siteID, "数据库", null, null);
        }

        public List<DeviceAndAlertModel> GetNetworkBySiteIDWithAlert(decimal siteID)
        {
            return GetDeviceBySiteIDAndTypeWithAlert(siteID, @"网络\安全设备", null, null);
        }

        public List<DeviceAndAlertModel> GetRoomBySiteIDWithAlert(decimal siteID)
        {
            return GetDeviceBySiteIDAndTypeWithAlert(siteID, "机房", null, null);
        }

        public DeviceModel GetDeviceByID(int deviceID)
        {
            return ModelHelper<DeviceModel>.GetModel_By_DirectSql(selectGetDeviceByID, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="id",OracleDbType=OracleDbType.Decimal,Value=deviceID},
                 },
        oda).FirstOrDefault();
        }


        public int Add(DeviceModel model)
        {
            return ModelHelper<DeviceModel>.AddModel_By_QueryParams(model, new List<string>() { "ID" }, oda);
        }



        public void Delete(decimal id)
        {
            ModelHelper<DeviceModel>.UpdateOrDeleteModel_By_DirectSql(sqlDelete, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="id",OracleDbType=OracleDbType.Decimal,Value=id},
                 },
     oda);
        }

        public int Update(DeviceModel model)
        {
            return ModelHelper<DeviceModel>.UpdateModel_By_QueryParams(new List<QueryParameter>(){
                new QueryParameter(){OracleDbColumnName="ID",OracleDbColumnValue=model.ID,OracleDbColumnType=OracleDbType.Decimal,
                    PredicateType=PredicateType.where,QueryOperator=QueryOperatorType.equal}
            }, model, new List<string>() { "ID", "CreateTime", "SiteId", "BusinessSystemId", "Identify", "BPPMId" }, oda);
        }


        public List<DeviceModel> GetDeviceByBSID(decimal bsID)
        {
            //参数一定要写全，否则会导致缓存 窜读。有些方法不能用缓存，比如权限，Device，BS的管理等。
            //string keyCache = CacheHelper.GetKey(GetType(),"GetDeviceByBSID", bsID);
            //object resultCache = CacheHelper.GetCache(keyCache);
            //if (resultCache != null) return (List<DeviceModel>)resultCache;
            // var result = ModelHelper<DeviceModel>.GetModel_By_DirectSql(sqlGetDeviceByBSID, new List<OracleParameter>()
            //  {
            //     new OracleParameter(){ ParameterName="BusinessSystemId",OracleDbType=OracleDbType.Decimal,Value=bsID},
            //  },
            //oda);
            //CacheHelper.SetCacheWithAbsoluteExpiration(keyCache, result);
            // return result;
            return ModelHelper<DeviceModel>.GetModel_By_DirectSql(sqlGetDeviceByBSID, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="BusinessSystemId",OracleDbType=OracleDbType.Decimal,Value=bsID},
                 },
     oda);
        }


        public List<DeviceModel> GetDevicePagedByBSID(decimal bsID, out decimal totalRecords, string identify = "", string type = "", int pageSize = -1, int pageIndex = -1,
            string sortName = "CreateTime", string sortDirection = "Descending")
        {
            List<OracleParameter> paramss = new List<OracleParameter>(){new OracleParameter(){ ParameterName="BusinessSystemId",
                OracleDbType=OracleDbType.Decimal,Value=bsID}};
            string sqlFormate = string.Empty;
            if (!string.IsNullOrWhiteSpace(identify))
            {
                sqlFormate += " and d.Identify=:Identify";
                paramss.Add(new OracleParameter()
                {
                    ParameterName = "Identify",
                    OracleDbType = OracleDbType.NVarchar2,
                    Size = 50,
                    Value = identify
                });
            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                sqlFormate += " and d.Type=:Type";
                paramss.Add(new OracleParameter()
                {
                    ParameterName = "Type",
                    OracleDbType = OracleDbType.NVarchar2,
                    Size = 50,
                    Value = type
                });
            }
            string sql = string.Format(sqlGetDeviceByBSID, sqlFormate);
            var copy = new List<OracleParameter>();
            paramss.ForEach(item => copy.Add((OracleParameter)(item.Clone())));
            totalRecords = ModelHelper<AlertModel>.GetModelCount_By_DirectSql(SQLHelper.GetCountSQLFormDirectSQL(sql), paramss, oda);
            return ModelHelper<DeviceModel>.GetModel_By_DirectSql(SQLHelper.GetPageSQLFormDirectSQL(sql, sortName, sortDirection,
                pageSize, pageIndex), copy, oda);
        }


        public List<DeviceAndCount> GetDeviceAlertCountBySiteID(decimal siteID)
        {
            //参数一定要写全，否则会导致缓存 窜读。有些方法不能用缓存，比如权限，Device，BS的管理等。
            //var ct = CacheHelper.TryGet<List<DeviceAndCount>>(GetType(),"GetDeviceByBSID", siteID);
            //if (string.IsNullOrEmpty(ct.Item1)) return ct.Item2;
            //var result = ModelHelper<DeviceAndCount>.GetModel_By_DirectSql(sqlGetDeviceAlertCountBySiteID, new List<OracleParameter>()
            //     {
            //         new OracleParameter(){ ParameterName="siteID",OracleDbType=OracleDbType.Decimal,Value=siteID},
            //     }, oda);
            //CacheHelper.SetCacheWithAbsoluteExpiration(ct.Item1, result);
            //return result;
            return ModelHelper<DeviceAndCount>.GetModel_By_DirectSql(sqlGetDeviceAlertCountBySiteID, new List<OracleParameter>()
               {
               new OracleParameter()
                {
                    ParameterName = "siteID",
                    OracleDbType = OracleDbType.Decimal,
                    Value = siteID
                }
               }, oda);
        }


        public DeviceSyncModel GetDeviceSyncModel(decimal deviceID)
        {
            return ModelHelper<DeviceSyncModel>.GetModel_By_DirectSql(sqlGetDeviceSiteGJIPPort2, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="deviceID",OracleDbType=OracleDbType.Decimal,Value=deviceID},
                 }, oda).FirstOrDefault();
        }

        public List<DeviceModel> GetNoBSIDDeviceByBSID(decimal bsID)
        {
            return ModelHelper<DeviceModel>.GetModel_By_DirectSql(sqlGetNoBSIDDeviceByBSID, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="BusinessSystemId",OracleDbType=OracleDbType.Decimal,Value=bsID},
                 },
oda);
        }

        public int UpdateNoBSID(DeviceModel model)
        {
            return ModelHelper<DeviceModel>.UpdateModel_By_QueryParams(new List<QueryParameter>(){
                new QueryParameter(){OracleDbColumnName="ID",OracleDbColumnValue=model.ID,OracleDbColumnType=OracleDbType.Decimal,
                    PredicateType=PredicateType.where,QueryOperator=QueryOperatorType.equal}
            }, model, new List<string>() { "ID", "CreateTime", "SiteId", "Identify", "Type", "Object", "SubObject", "BPPMId" }, oda);
        }

        public bool GetIsExistNoBSID(DeviceModel model)
        {
            List<OracleParameter> listP = new List<OracleParameter>()
            {
                     new OracleParameter(){ ParameterName="SiteID",OracleDbType=OracleDbType.Decimal,Value=model.SiteId},
                     new OracleParameter(){ ParameterName="Identify",OracleDbType=OracleDbType.NVarchar2,Value=model.Identify},
                     new OracleParameter(){ ParameterName="Type",OracleDbType=OracleDbType.NVarchar2,Value=model.Type}
            };
            string sql = sqlGetIsExistNoBSID;

            if (!string.IsNullOrWhiteSpace(model.Object))
            {
                sql += " and d.object=:Object ";
                listP.Add(new OracleParameter() { ParameterName = "Object", OracleDbType = OracleDbType.NVarchar2, Value = model.Object });
            }
            if (!string.IsNullOrWhiteSpace(model.SubObject))
            {
                sql += " and d.subobject=:Subobject ";
                listP.Add(new OracleParameter() { ParameterName = "Subobject", OracleDbType = OracleDbType.NVarchar2, Value = model.SubObject });
            }
            return ModelHelper<DeviceModel>.GetModelCount_By_DirectSql(sql, listP, oda) != 0;
        }

        public bool GetIsExist(DeviceModel model)
        {
            List<OracleParameter> listP = new List<OracleParameter>()
            {
                     new OracleParameter(){ ParameterName="SiteID",OracleDbType=OracleDbType.Decimal,Value=model.SiteId},
                     new OracleParameter(){ ParameterName="Identify",OracleDbType=OracleDbType.NVarchar2,Value=model.Identify},
                      new OracleParameter(){ ParameterName="BusinessSystemID",OracleDbType=OracleDbType.Decimal,Value=model.BusinessSystemId},
                     new OracleParameter(){ ParameterName="Type",OracleDbType=OracleDbType.NVarchar2,Value=model.Type}
            };

            string sql = sqlGetIsExist;

            if (!string.IsNullOrWhiteSpace(model.Object))
            {
                sql += " and d.object=:Object ";
                listP.Add(new OracleParameter() { ParameterName = "Object", OracleDbType = OracleDbType.NVarchar2, Value = model.Object });
            }
            if (!string.IsNullOrWhiteSpace(model.SubObject))
            {
                sql += " and d.subobject=:Subobject ";
                listP.Add(new OracleParameter() { ParameterName = "Subobject", OracleDbType = OracleDbType.NVarchar2, Value = model.SubObject });
            }

            return ModelHelper<DeviceModel>.GetModelCount_By_DirectSql(sql, listP, oda) != 0;
        }

    }
}
